﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;


using Galaxy.Vinder.BO;

namespace Galaxy.Vinder.DAL
{
    public class BannerDB
    {
        private static String connectionString = AppConfiguration.ConnectionString;

        public static Banner getBanner(int id)
        {
            Banner banner= null;
            String cmd = "select id, imagen,imagen from banner where id= " + id;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(cmd, connection);
                command.CommandType = CommandType.Text;

                connection.Open();
                using (SqlDataReader myReader = command.ExecuteReader())
                {
                    if (myReader.Read())
                    {
                        banner = FillDataRecord(myReader);
                    }
                    myReader.Close();
                }
                connection.Close();
            }
            return banner;
        }

        public static List<Banner> getTodosLosBanner()
        {
            List<Banner> banners= null;
            String cmd = "select * from banner";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(cmd, connection);
                command.CommandType = CommandType.Text;

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        banners= new List<Banner>();
                        while (reader.Read())
                        {
                            banners.Add(FillDataRecord(reader));
                        }
                    }
                    reader.Close();
                }
                connection.Close();
            }
            return banners;
        }

        public static long salvar(Banner banner)
        {
            long insertID = -1;
            string sqlIns;
            bool insert = false;
            if (banner.Id == -1)
            {
                sqlIns = "INSERT INTO banner (imagen, orden) VALUES (@imagen, @orden)";
                insert = true;
            }
            else
            {
                sqlIns = "update banner set imagen = @imagen, orden = @orden where id=" + banner.Id;
            }

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmdIns = new SqlCommand(sqlIns, connection);
                connection.Open();
                cmdIns.Parameters.AddWithValue("@imagen", banner.Imagen);
                cmdIns.Parameters.AddWithValue("@orden", banner.Orden);
                try
                {
                    cmdIns.ExecuteNonQuery();
                }
                catch (SqlException error)
                {

                }

                cmdIns.Parameters.Clear();
                cmdIns.CommandText = "SELECT @@IDENTITY";
                if (insert)
                {
                    insertID = Convert.ToInt64(cmdIns.ExecuteScalar());
                }
                else
                {
                    insertID = banner.Id;
                }
                cmdIns.Dispose();
                cmdIns = null;
                connection.Close();
            }
            return insertID;
        }

        public static bool borrar(int id)
        {
            int result = 0;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                String cmd = "delete from banner where id= " + id;
                SqlCommand command = new SqlCommand(cmd, connection);
                command.CommandType = CommandType.Text;
                connection.Open();
                result = command.ExecuteNonQuery();
                connection.Close();
            }
            return result > 0;
        }

        private static Banner FillDataRecord(IDataRecord data)
        {
            Banner banner = new Banner();
            banner.Id = data.GetInt64(data.GetOrdinal("id"));
            if (!data.IsDBNull(data.GetOrdinal("imagen")))
            {
                banner.Imagen = data.GetString(data.GetOrdinal("imagen"));
            }
            banner.Orden = data.GetInt32(data.GetOrdinal("orden"));
            return banner;
        }
    }
}
